﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using ExamSystem.Mdeol;
namespace ExamSystem.DAL
{
    public class TpagerDAL
    {


        /// <summary>
        /// 根据名称查询是否有试卷
        /// </summary>
        public int selecttpname(string tpname)
        {
            string sql = "select * from tpager where tpName='" + tpname + "'";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            return dt.Rows.Count;
        }



        /// <summary>
        /// 根据试卷编号删除数据
        /// </summary>
        public bool delete(int id)
        {
            string sql1 = "delete from tanswer where tpid="+id;
            string sql2 = "delete from tpagerstudent where tpid="+id;
            string sql3 = "delete from tpagerquestion where tpid=" + id;
            string sql4 = "delete from Tpager where tpid=" + id;
            List<string> list = new List<string>();
            list.Add(sql1);
            list.Add(sql2);
            list.Add(sql3);
            list.Add(sql4);
            return DBhepter.ExecuteTransaction(list);
        }



        /// <summary>
        /// 根据试卷编号查询是否有数据
        /// </summary>
        public Tpager selecttpid(int tpid)
        {
            Tpager ts = null;
            string sql = "select *,(select sum(tqscore) from tquestion where tqid in(select tqid from tpagerquestion b where tpid=a.tpid)) tpscore,(select count(*) from tpagerquestion b where tpid=a.tpid) tpcount from tpager a where tpid="+tpid;
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            if (dt.Rows.Count > 0)
            {
                DataRow dr = dt.Rows[0];
                int tpId = (int)dr["tpid"];
                string tpname = dr["tpname"].ToString();
                string TpBeginTime = dr["TpBeginTime"].ToString();
                string TpEndTime = dr["TpEndTime"].ToString();
                int tpscore = dr["tpscore"].ToString() != "" ? (int)dr["tpscore"] : 0;
                int tpcount = (int)dr["tpcount"];
                ts = new Tpager(tpId, tpname, TpBeginTime, TpEndTime,tpscore,tpcount);
            }
            return ts;
        }



        /// <summary>
        /// 根据课程编号修改数据
        /// </summary>
        public int update(Tpager t)
        {

            string sql = "update Tpager set tpname='" + t.TpName + "',TpBeginTime='" + t.TpBeginTime + "',TpEndTime='"+t.TpEndTime+"' where tpid=" + t.TpId;
            return DAL.DBhepter.ExecuteNonQuery(sql);
        }


        /// <summary>
        /// 查询所有信息
        /// </summary>
        public List<Tpager> queryall()
        {
            List<Tpager> list = new List<Tpager>();
            string sql =" select *,(select sum(tqscore) from tquestion where tqid in(select tqid from tpagerquestion b where tpid=a.tpid)) tpscore,(select count(*) from tpagerquestion b where tpid=a.tpid) tpcount from tpager a";
            DataSet ds = DBhepter.GetDataSet(sql);
            DataTable dt = ds.Tables[0];
            for (int i = 0; i < dt.Rows.Count;i++ )
            {
                DataRow dr = dt.Rows[i];
                int tpid = (int)dr["tpid"];
                string tpname = dr["tpname"].ToString();
                string tpbegintime = dr["tpbegintime"].ToString();
                string tpendtime = dr["tpendtime"].ToString();
                int tpscore =dr["tpscore"].ToString()!=""?(int)dr["tpscore"]:0;
                int tpcount = (int)dr["tpcount"];
                Tpager t = new Tpager(tpid,tpname,tpbegintime,tpendtime,tpscore,tpcount);
                list.Add(t);
            }
            return list;
        }



        //添加试卷信息
        public int insert(Tpager t)
        {
            string sql = "insert into tpager values('"+t.TpName+"','"+t.TpBeginTime+"','"+t.TpEndTime+"')";
            return DBhepter.ExecuteNonQuery(sql);
        }


        //删除学生试卷表信息
        public bool updatetpagerstudent(int tpid,string stuids)
        {
            List<string> sqls = new List<string>();
            string sql1 = "delete from tpagerstudent where tpid="+tpid;
            sqls.Add(sql1);
            if(!string.IsNullOrEmpty(stuids))
            {
                foreach(string stuid in stuids.Split(','))
                {
                    string sql2 = "insert into tpagerstudent values(" + tpid + "," + stuid + ",0)";
                    sqls.Add(sql2);
                }
            }
            return DBhepter.ExecuteTransaction(sqls);
        }




        //删除试卷试题表信息
        public bool updatetpagerquestion(int tpid, string stuids)
        {
            List<string> sqls = new List<string>();
            string sql1 = "delete from tpagerquestion where tpid=" + tpid;
            sqls.Add(sql1);
            if (!string.IsNullOrEmpty(stuids))
            {
                foreach (string stuid in stuids.Split(','))
                {
                    string sql2 = "insert into tpagerquestion values(" + tpid + "," + stuid + ")";
                    sqls.Add(sql2);
                }
            }
            return DBhepter.ExecuteTransaction(sqls);
        }



        
       
    }
}